package com.zlt.dao.Impl;

import com.zlt.dao.ChatMessageDao;
import com.zlt.entity.ChatMessageDetail;
import com.zlt.entity.ChatMessageMain;
import com.zlt.util.JDBCUtil;
import com.zlt.util.StringUtil;
import com.zlt.vo.ChatVo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.UUID;


public class ChatMessageDaoImpl implements ChatMessageDao {

    private final JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtil.getDataSource());
    private final QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());

    @Override
    public List<ChatVo> showSeekerChatList(String username) {
        String sql = "SELECT s.nickname AS seekerName,s.photo AS seekerPhoto,r.nickName AS recruitersName,r.username AS phone,r.photo AS recruitersPhoto,c.seeker_delete_status AS cid,c.recruiters_delete_status AS jid,(SELECT COUNT(*) FROM chat_message_detail AS d WHERE c.id = d.main_id AND has_read = 0 AND d.send_user = (SELECT uid FROM user WHERE username = ? ) ) AS num FROM chat_message_main AS c,user AS s,user AS r WHERE c.job_seeker = s.uid AND c.recruiters = r.uid AND c.job_seeker = (SELECT uid FROM user WHERE username = ? )";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(ChatVo.class), username, username);
    }

    @Override
    public List<ChatVo> showRecruiterChatList(String username) {
        String sql = "SELECT s.nickname AS seekerName,s.photo AS seekerPhoto,r.nickName AS recruitersName,s.username AS phone,r.photo AS recruitersPhoto,c.seeker_delete_status AS cid,c.recruiters_delete_status AS jid,( SELECT COUNT(*) FROM chat_message_detail AS d WHERE c.id = d.main_id AND has_read = 0 AND d.send_user = ( SELECT uid FROM user WHERE username = ? ) ) AS num FROM chat_message_main AS c,user AS s,user AS r WHERE c.job_seeker = s.uid AND c.recruiters = r.uid AND c.recruiters = ( SELECT uid FROM user WHERE username = ? )";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(ChatVo.class), username, username);
    }

    @Override
    public int insertMainByParams(UUID uuid1, UUID uuid2) {
        String sql = "INSERT INTO chat_message_main(job_seeker,recruiters)VALUES(?,?)";
        return jdbcTemplate.update(sql, uuid1, uuid2);
    }

    @Override
    public int insertMainByClass(ChatMessageMain chatMessageMain) {
        String sql = "INSERT INTO chat_message_main(id,job_seeker,seeker_delete_status,recruiters,recruiters_delete_status)VALUES(?,?,?,?,?)";
        return jdbcTemplate.update(sql, getMainMaxId(), chatMessageMain.getJobSeeker(), chatMessageMain.getSeekerDeleteStatus(), chatMessageMain.getRecruiters(), chatMessageMain.getRecruitersDeleteStatus());
    }

    @Override
    public int insertDetailByParams(int mainId, String thisUUID, String userUid, Character type, Object content, Date date, Character hasRead) {
        return 0;
    }

    @Override
    public int insertDetailByClass(ChatMessageDetail chatMessageDetail) {
        return 0;
    }

    @Override
    public ChatMessageMain isExist(String src, String dest) {
        String sql = "SELECT id,recruiters_delete_status AS recruitersDeleteStatus FROM chat_message_main WHERE  job_seeker=? AND recruiters=?";
        try {
            return runner.query(sql, new BeanHandler<>(ChatMessageMain.class), src, dest);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer insertRecord(String src, String dest) {
        String sql = "INSERT INTO chat_message_main(job_seeker,recruiters) VALUES(?,?)";
        try {
            int update = runner.update(sql, src, dest);
            if (update > 0) {
                String idSql = "SELECT id FROM chat_message_main WHERE  job_seeker=? AND recruiters=?";
                return runner.query(idSql, new ScalarHandler<>(1), src, dest);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return null;
    }

    @Override
    public Integer insertDetailByParams(Integer mainId, String uid, String to, String content, Character hasRead) throws SQLException {
        String sql = "INSERT INTO chat_message_detail(main_id,send_user,receive_user,content,create_time,has_read) VALUE(?,?,?,?,?,?)";
        return runner.update(sql, mainId, uid, to, content, StringUtil.getCurrentTime(), String.valueOf(hasRead));
    }

    @Override
    public List<ChatMessageMain> selectChatList(String uid) throws SQLException {
        String sql = "SELECT recruiters FROM `chat_message_main` WHERE job_seeker=? AND recruiters_delete_status=0";
        return runner.query(sql, new BeanListHandler<>(ChatMessageMain.class), uid);
    }

    @Override
    public List<ChatMessageDetail> selectChatDetail(String src, String dest) throws SQLException {
        String sql = "SELECT send_user sendUser,receive_user receiveUser,content,create_time createTime FROM `chat_message_detail` WHERE send_user=? AND receive_user=?";

        return runner.query(sql, new BeanListHandler<>(ChatMessageDetail.class), src, dest);
    }

    @Override
    public Integer addMain(String user1, String user2, String cid, String jid) {
        String sql = "INSERT INTO chat_message_main(job_seeker,seeker_delete_status,recruiters,recruiters_delete_status) VALUES((SELECT uid FROM user WHERE username = ? ),?,(SELECT uid FROM user WHERE username = ? ),?)";
        if (jdbcTemplate.update(sql, user1, user2) > 0) {
            return jdbcTemplate.queryForObject("SELECT id FROM chat_message_main WHERE job_seeker = (SELECT uid FROM user WHERE username = ? ) AND recruiters = (SELECT uid FROM user WHERE username = ? )", Integer.class, user1, cid, user2, jid);
        } else {
            return -1;
        }
    }

    @Override
    public List<ChatMessageDetail> showChats(String phone1, String phone) {
        String sql = "SELECT d.id AS id,d.main_id AS mianId,s.username AS sendUser,r.username AS receiveUser,d.content_type AS contentType,d.content AS content,d.create_time AS createTime,d.has_read AS hasRead FROM chat_message_detail AS d, chat_message_main AS m ,user AS s,user AS r WHERE d.main_id = m.id AND s.uid = d.send_user AND r.uid = d.receive_user AND m.id = ( SELECT id FROM chat_message_main WHERE job_seeker = (SELECT uid FROM user WHERE username = ? ) AND recruiters = (SELECT uid FROM user WHERE username = ? ) )";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(ChatMessageDetail.class), phone1, phone);
    }

    @Override
    public Integer getMain(String uid1, String uid2) {
        String sql = "SELECT id FROM chat_message_main WHERE job_seeker = ? AND recruiters = ? ";
        return jdbcTemplate.queryForObject(sql, Integer.class, uid1, uid2);
    }

    private int getMainMaxId() {
        String sql = "SELECT (IFNULL(MAX(id),0)+1) AS maxid FROM chat_message_main ORDER BY id";
        return jdbcTemplate.queryForObject(sql, int.class);
    }

    private int getDetailMaxId() {
        String sql = "SELECT (IFNULL(MAX(id),0)+1) AS maxid FROM chat_message_detail ORDER BY id";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }
}
